﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;

namespace NTTDATA.BEENChina.ResultCertification.DataStore
{
    public class TestDataAccess
    {
        private static string connStr  
        {
            get
            {
                return System.Configuration.ConfigurationManager.AppSettings["ResultCertificationDB"];
            }
        }

        public void InsertTestData(  List<string[]> datas)
        { 
            string sql = " INSERT INTO  [store_test] "+
           " ( OrderID, ImageID, FormID , CategoryID,   AResult, AEntryCommentID,  BResult, BEntryCommentID,  CResult, CEntryCommentID,  DResult, DEntryCommentID ,   FinalResult, FinalEntryCommentID )      " +
            " VALUES    (@OrderID, @ImageID, @FormID , @CategoryID,  @AResult, @AEntryCommentID,  @BResult, @BEntryCommentID,  @CResult, @CEntryCommentID,  @DResult, @DEntryCommentID ,    @FinalResult, @FinalEntryCommentID)     ";
            
           
            SqlConnection con = new SqlConnection(connStr);
            con.Open();
            SqlTransaction tra = con.BeginTransaction();
            try
            {
                foreach (string[] data in datas)
                {
                    SqlParameter[] sqlParameters = 
                    {
                        new SqlParameter("@OrderID",data[0]),
                        new SqlParameter("@ImageID" ,data[1]),
                        new SqlParameter("@FormID" ,data[2]),
                        new SqlParameter("@CategoryID" ,data[3]),
                        new SqlParameter("@AResult"  ,data[4]),
                        new SqlParameter("@AEntryCommentID" ,data[5]),
                        new SqlParameter("@BResult" ,data[6]),
                        new SqlParameter("@BEntryCommentID",data[7]),
                        new SqlParameter("@CResult" ,data[8]),
                        new SqlParameter("@CEntryCommentID",data[9]),
                        new SqlParameter("@DResult",data[10]),
                       new SqlParameter("@DEntryCommentID",data[11]),
                        new SqlParameter("@FinalResult",data[12]),
                        new SqlParameter("@FinalEntryCommentID" ,data[13])                 
                    };
                    SqlHelper.ExecuteNonQuery(tra, CommandType.Text, sql, sqlParameters);
                    
                }
              
                tra.Commit();
            }
            catch (Exception exp)
            {
                tra.Rollback();
                throw exp;
            }
            finally
            {
                con.Close();
            }

        }

        public void InsertCategoryVerfityItem(SqlTransaction tra,string categoryId,string vertifyId)
        {
            string sql = " INSERT INTO  [CategoryVerfityItem] " +
           " ([CategoryID] " +
           " ,[VerfityItemID]) " +
           " VALUES " +
           " (@CategoryID " +
           " ,@VerfityItemID) ";
            SqlParameter[] sqlParameters = 
            {
                new SqlParameter("@CategoryID", categoryId),
                new SqlParameter("@VerfityItemID", vertifyId)
            };
            SqlHelper.ExecuteNonQuery(tra, CommandType.Text, sql, sqlParameters);
        }

        public void UpdateVertifySelect(SqlTransaction tra, string vertifyId)
        {
            string sql = "  update VerfityItem set Selected = 0; " +
                        " update VerfityItem set Selected=1 where VerfityItemID =@Id ";
            SqlParameter[] sqlParameters = 
            {
                new SqlParameter("@Id", vertifyId),            
            };

            SqlHelper.ExecuteNonQuery(tra, CommandType.Text, sql, sqlParameters);
        }
    }
}
